package com.bj58.groupbuy.action.inter;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.bj58.groupbuy.action.db.CommonDao;
import com.bj58.groupbuy.action.db.DBConfig;
import com.bj58.groupbuy.action.model.AllianceDataSummaryVo;
import com.bj58.groupbuy.action.util.DateUtil;
import com.bj58.groupbuy.action.util.FileUtil;
import com.bj58.groupbuy.action.util.SomePublicUtil;
import com.bj58.groupbuy.util.PathUtil;

/**在线收入数据汇总
 * @author sunny3791
 *
 */
public class AllianceDataSummary extends CommonDataDeal{
	private CommonDao comDao = new CommonDao();
	private FileUtil fileUtil = new FileUtil();
	
	// for 58
	private double allIncome58 = 0 ;//联盟总收入 -58
	private double googleAFS58 = 0;  //google  AFS 广告总收入 -58
	private double googleAFC58 = 0;  //google  AFC 广告总收入 -58
	
	private double baiduAFS58 = 0;  //百度  AFS 广告总收入  -58
	private double baiduAFC58 = 0;  //百度  AFC 广告总收入 -58
	private double speBaiduAFC = 0 ; // 百度特殊计费名AFC收入 - 58
	private double speBaiduAFS = 0 ; // 百度特殊计费名AFS收入 - 58
	private double baiduAPP58 = 0; //百度APP收入 -58
	
	
	//百度特殊处理计费名
	private String speAFSName = "'14026035_17_pg','14026035_26_pg','14026035_48_pg','14026035_49_pg','14026035_53_pg'";
	private String speAFCName = "'14026035_17_cpr','14026035_26_cpr','14026035_48_cpr','14026035_49_cpr','14026035_53_cpr'";
	
	private double sougouIncome = 0;//搜狗总收入 (正常+特殊）
	private double speSouGouAFS = 0 ; // 搜狗特殊计费名AFS收入 - 58
	private String speSouGouAFSName = "'58tongcheng_ss6','58tongcheng_ss8','58tongcheng_ss9'";
	
	//for 驾校一点通(jxydt)
	private double googleAFSJxydt = 0;  //google  AFS 广告总收入 -jxydt
	private double googleAFCJxydt = 0;  //google  AFC 广告总收入 -jxydt
	private double allIncomeJxydt = 0 ;//联盟总收入 -jxydt
	
	//58摊销总收入的所有memberId
	String memberIdAll58 = "1019,1022,1028,1032,1034,1037,1038,1039,1071,1077,1081,1082,1092,1094,1096," +
			"1097,1104,1105,1106,1108,1126,7023,7202,1114,1115,1116," +
			"1117,1118,1119,1120,1121,1088,1113,1127,1128,1146,1147,1155,1156,1152,1164,1165,1149";

	
	/**计算GOOGLE的广告收入(58/jxydt)
	 * @param date
	 * @param source  来源  = 58/jxydt
	 */
	private void  countGoogle(String date,String source)throws Exception{
		StringBuffer sql = new StringBuffer();
		sql.append(" select advertType,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"'  ");
		sql.append(" and allianceName  = '谷歌' and  billingName = '总计'  " );
		sql.append(" and source = '" + source + "' ");
		sql.append(" group by advertType  ");
		
		Map<String,String>  rsMap = comDao.selectValueAsMap(sql.toString(), DBConfig.db_68_ors.getIntType());
		
		if("58".equals(source)){
			if(rsMap.containsKey("AFS")){
				googleAFS58 = Double.parseDouble( rsMap.get("AFS") );
			}
			if(rsMap.containsKey("AFC")){
				googleAFC58 = Double.parseDouble( rsMap.get("AFC") );
			}
		}else if("jxydt".equals(source)){
			if(rsMap.containsKey("AFS")){
				googleAFSJxydt = Double.parseDouble( rsMap.get("AFS") );
			}
			if(rsMap.containsKey("AFC")){
				googleAFCJxydt = Double.parseDouble( rsMap.get("AFC") );
			}
		}
		
	
		
	}
	
	
	/**计算58的百度广告收入
	 * @param date
	 */
	private void countBaiduFor58(String date)throws Exception{
		/*AFC*/
		StringBuffer sqlAFC = new StringBuffer();
		sqlAFC.append("select sum(amount) amount from alliance_billing_metrics  ");
		sqlAFC.append("where statDate = '"+date+"' ");
		sqlAFC.append("and allianceName ='百度' ");
		sqlAFC.append("and billingName not in('wm_all','ss_all',"+speAFCName+","+speAFSName+ ")  ");
		sqlAFC.append("and channel ='总计' ");
		sqlAFC.append("and advertType = 'AFC' ");
		sqlAFC.append("and source ='58' ");
		
		baiduAFC58 = comDao.selectDouble(sqlAFC.toString(), DBConfig.db_68_ors.getIntType()) + speBaiduAFC;
		
		
		/*AFS*/
		StringBuffer sqlAFS = new StringBuffer();
		sqlAFS.append("select sum(amount) amount from alliance_billing_metrics  ");
		sqlAFS.append("where statDate = '"+date+"' ");
		sqlAFS.append("and allianceName ='百度' ");
		sqlAFS.append("and billingName not in('wm_all','ss_all',"+speAFCName+","+speAFSName+ ")  ");
		sqlAFS.append("and channel ='总计' ");
		sqlAFS.append("and advertType = 'AFS' ");
		sqlAFS.append("and source ='58' ");
		
		baiduAFS58 = comDao.selectDouble(sqlAFS.toString(), DBConfig.db_68_ors.getIntType()) + speBaiduAFS;
		
		
		/*APP*/
		StringBuffer sqlAPP = new StringBuffer();
		sqlAPP.append("select sum(amount) amount from alliance_billing_metrics  ");
		sqlAPP.append("where statDate = '"+date+"' ");
		sqlAPP.append("and allianceName ='百度' ");
		sqlAPP.append("and billingName ='app_all' ");
		sqlAPP.append("and channel ='总计' ");
		sqlAPP.append("and advertType = 'app' ");
		sqlAPP.append("and source ='58' ");
		
		baiduAPP58 = comDao.selectDouble(sqlAPP.toString(), DBConfig.db_68_ors.getIntType());
	}
	
	
	/**计算58的搜狗广告收入
	 * @param date
	 */
	private void countSougouFor58(String date)throws Exception{

		//搜狗 的正常收入总额
		StringBuffer sql = new StringBuffer();
		sql.delete(0, sql.length()+1);
		sql.append(" select sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"' " );
		sql.append(" and  allianceName  = '搜狗' ");
		sql.append(" and billingName not in(" + speSouGouAFSName+ ")  ");
		sql.append(" and source = '58' ");
		
		sougouIncome = comDao.selectDouble(sql.toString(), DBConfig.db_68_ors.getIntType()) + speSouGouAFS;
	}
	

	
	
	
	/**计算58特殊计费名的分成值
	 * @param date
	 * @param type	 计算月总值的方式   0：预估   1：加和已有的整月收入 （数据已全部上传，无需估算）
	 * @throws Exception
	 */
	private void dealSpecFor58(String date,int type)throws Exception{
		/** 1.  获取月初 - 今日的 特殊计费名总收入  
		 * 	 2.  总收入 / （月初 - 至今的总天数）   =  日均值
		 *   3.  日均值 ×　月总天数　 =  月预估总值
		 *   4.  根据月预估总值判断区间：　　　　 
		 *  	40%，如当月合作收入小于等于30万元人民币，则分成比例为【40】%
		 *  	30%，如当月合作收入大于30万元且小于等于60万元人民币，则按照【30】%分成。
		 * 		20%-30%，如当月合作收入大于60万元人民币，则60万元按【30】%分成，60万元以上部分按【20】%分成。
		 */
		
		//当日百度AFS特殊计费名收入
		StringBuffer sqlBaidu = new StringBuffer();
		sqlBaidu.append(" select sum(amount) amount from alliance_billing_metrics  ");
		sqlBaidu.append(" where statDate = '"+date+"'");
		sqlBaidu.append(" and allianceName ='百度' ");
		sqlBaidu.append(" and billingName in(" + speAFSName + " )");
		sqlBaidu.append(" and source ='58' ");
		
		speBaiduAFS = comDao.selectDouble(sqlBaidu.toString(), DBConfig.db_68_ors.getIntType()); 
		
		
		//当日百度AFC特殊计费名收入
		sqlBaidu.delete(0, sqlBaidu.length()+1);
		sqlBaidu.append(" select sum(amount) amount from alliance_billing_metrics  ");
		sqlBaidu.append(" where statDate = '"+date+"'");
		sqlBaidu.append(" and allianceName ='百度' ");
		sqlBaidu.append(" and billingName in(" + speAFCName +" )");
		sqlBaidu.append(" and source ='58' ");
		
		speBaiduAFC = comDao.selectDouble(sqlBaidu.toString(), DBConfig.db_68_ors.getIntType()); 
		
		
		//当日搜狗AFS特殊计费名收入
		StringBuffer sqlSougou = new StringBuffer();
		sqlSougou.append(" select sum(amount) amount from alliance_billing_metrics  ");
		sqlSougou.append(" where statDate = '"+date+"'");
		sqlSougou.append(" and allianceName ='搜狗' ");
		sqlSougou.append(" and billingName in(" + speSouGouAFSName + " )");
		sqlSougou.append(" and source ='58' ");
		
		speSouGouAFS= comDao.selectDouble(sqlSougou.toString(), DBConfig.db_68_ors.getIntType()); 
		
		
		
		//截止今日当月累加收入
		String monthFirstDay = date.substring(0,date.lastIndexOf("-"))+"-01";
		StringBuffer sqlSpeAll = new StringBuffer();
		double valueSpeAll = 0;
		
		sqlSpeAll.append(" select sum(amount) amount from alliance_billing_metrics  ");
		sqlSpeAll.append(" where statDate <= '"+date+"'  and statDate >='"+monthFirstDay+"' ");
		sqlSpeAll.append(" and allianceName in('百度','搜狗') ");
		sqlSpeAll.append(" and billingName in("+ speAFCName+","+speAFSName+","+speSouGouAFSName+" )");
		sqlSpeAll.append(" and source ='58' ");
		
		valueSpeAll = comDao.selectDouble(sqlSpeAll.toString(), DBConfig.db_68_ors.getIntType()); //当月(至今)总收入（未分成）
		
		
		//计算至今日均值  估算月总额
		int monthDaysNum = DateUtil.getDaysByMonth(date); //当月总天数
		long subDay = DateUtil.getDistDates(DateUtil.strToDate(monthFirstDay, "yyyy-MM-dd"), DateUtil.strToDate(date, "yyyy-MM-dd")) +1;  //已过天数
		
		double EstimateMonthIncome  = 0;
		if(type == 0){
			 EstimateMonthIncome = (valueSpeAll/subDay ) * monthDaysNum; //预估月总收入(未分成前)
		}else if(type == 1){
			EstimateMonthIncome = sumSpeMonthIncomeFor58(date); //根据已上传的月数据获取月总额
		}
		
		if(EstimateMonthIncome < 300000){  //0.4
			speBaiduAFS   =  speBaiduAFS * 0.4;
			speBaiduAFC   =  speBaiduAFC * 0.4;
			speSouGouAFS   =  speSouGouAFS * 0.4;
		}else if(EstimateMonthIncome >= 300000 && EstimateMonthIncome < 600000){  //0.3
			speBaiduAFS   =  speBaiduAFS * 0.3;
			speBaiduAFC   =  speBaiduAFC * 0.3;
			speSouGouAFS   =  speSouGouAFS * 0.3;
		}else if(EstimateMonthIncome >= 600000){
			 //百度分成收入 =  百度收入 * （ （月预估总值 - 60W）/月预估总值  ） * 0.2 +　  百度收入＊（60W/月预估总值 ）＊0.3
			speBaiduAFS = speBaiduAFS * (( EstimateMonthIncome - 600000 ) / EstimateMonthIncome) * 0.2  +   speBaiduAFS *  (600000/EstimateMonthIncome) * 0.3;
			speBaiduAFC = speBaiduAFC * (( EstimateMonthIncome - 600000 ) / EstimateMonthIncome) * 0.2  +   speBaiduAFC *  (600000/EstimateMonthIncome) * 0.3;
			speSouGouAFS = speSouGouAFS * (( EstimateMonthIncome - 600000 ) / EstimateMonthIncome) * 0.2  +   speSouGouAFS *  (600000/EstimateMonthIncome) * 0.3;
		}
	}
	
	
	/** 计算特殊计费名整月收入总值
	 * @return
	 */
	private double sumSpeMonthIncomeFor58(String date) throws Exception{
		String monthFirstDate = date.substring(0,date.lastIndexOf("-"))+"-01";
		
		int monthDaysNum = DateUtil.getDaysByMonth(date); //当月总天数
		String monthLastDate = date.substring(0,date.lastIndexOf("-"))+"-"+monthDaysNum;
		
		StringBuffer sqlSpeAll = new StringBuffer();
		
		sqlSpeAll.append(" select sum(amount) amount from alliance_billing_metrics  ");
		sqlSpeAll.append(" where statDate <= '"+monthLastDate+"'  and statDate >='"+monthFirstDate+"' ");
		sqlSpeAll.append(" and allianceName in('百度','搜狗') ");
		sqlSpeAll.append(" and billingName in("+ speAFCName+","+speAFSName+","+speSouGouAFSName+" )");
		sqlSpeAll.append(" and source ='58' ");
		
		double valueBaiduAll = comDao.selectDouble(sqlSpeAll.toString(), DBConfig.db_68_ors.getIntType()); //当月(至今)总收入（未分成）
		
		return valueBaiduAll;
	}
	

	/**计算58 联盟总收入
	 * 
	 * @param date
	 * @throws Exception
	 */
	private void countAllIncomeFor58(String date)throws Exception{
		StringBuffer sql = new StringBuffer();
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"'  ");
		sql.append(" and  allianceName  not in ('谷歌','百度','搜狗')  " );
		sql.append(" and source ='58' ");
		
		Map<String,String>  rsMap = comDao.selectValueAsMap(sql.toString(), DBConfig.db_68_ors.getIntType());
		
		double other = 0 ;
		if(rsMap.containsKey(date)){
			other = Double.parseDouble ( rsMap.get(date) );
		}
		
		allIncome58 = other + googleAFS58 + googleAFC58 + baiduAFS58 + baiduAFC58 + baiduAPP58 + sougouIncome;
	}
	
	
	//58联盟数据
	private Map<String,List<String>>  allianceDataSqlFor58(String date,int type)throws Exception{
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		//各联盟渠道的总展现量、点击
		sql.delete(0, sql.length()+1);
		sql.append(" select statDate,sum(showPv),sum(clicks)  from alliance_billing_metrics  ");
		sql.append(" where statDate = '"+date+"'  ");
		sql.append(" and (   " );
		sql.append(" allianceName not in ('百度','谷歌') or   ");
		sql.append(" (allianceName = '百度' and billingName in ('ss_all','wm_all','app_all')) or ");
		sql.append(" (allianceName = '谷歌' and billingName = '总计')  )");
		sql.append(" and source = '58' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols3 = new ArrayList<String>();
		cols3.add("statDate");
		cols3.add("showPvAll");
		cols3.add("clicksAll");
		sqlMap.put(sql.toString(),cols3 );
		
		//广告PV
		sql.delete(0, sql.length()+1);
		sql.append(" select statDate,total ");
		sql.append(" from flux_all_day  ");
		sql.append(" where statDate=' "+date+"' " );
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
	
		List<String> cols4 = new ArrayList<String>();
		cols4.add("statDate");
		cols4.add("adverPv");
		sqlMap.put(sql.toString(),cols4 );
		
		
		//淘宝收入
		sql.delete(0, sql.length()+1);
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"' " );
		sql.append(" and  allianceName ='淘宝'  ");
		sql.append(" and source = '58' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols6 = new ArrayList<String>();
		cols6.add("statDate");
		cols6.add("taobaoIncome");
		sqlMap.put(sql.toString(),cols6 );
		
		//整站PV
		sql.delete(0, sql.length()+1);
		sql.append(" select '"+date+"',sum(if(page_type='all',pv,0)) 'pv' ");
		sql.append(" FROM t_common_netflow_board ");
		sql.append(" where stat_date = '" + date + "' ");
		sql.append(" and business_name = 'all' ");
		
		sql.append(" :"+DBConfig.db_131_data_bi.getIntType());
		
		List<String> cols8 = new ArrayList<String>();
		cols8.add("statDate");
		cols8.add("allWebPv");
		sqlMap.put(sql.toString(),cols8 );
		
		
//		// 搜狗 的收入总额
//		sql.delete(0, sql.length()+1);
//		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
//		sql.append(" where statDate = '"+date+"' " );
//		sql.append(" and  allianceName in ('搜狗') ");
//		sql.append(" and source = '58' ");
//		sql.append(" :"+DBConfig.db_68_ors.getIntType());
//		
//		List<String> cols9 = new ArrayList<String>();
//		cols9.add("statDate");
//		cols9.add("sougouIncome");
//		sqlMap.put(sql.toString(),cols9 );
		
		
		// 亿玛 的收入总额
		sql.delete(0, sql.length()+1);
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"' " );
		sql.append(" and  allianceName in ('亿玛') ");
		sql.append(" and source = '58' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols10 = new ArrayList<String>();
		cols10.add("statDate");
		cols10.add("yimaIncome");
		sqlMap.put(sql.toString(),cols10 );
		
		
		// 谷歌ADX的收入总额
		sql.delete(0, sql.length()+1);
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"' " );
		sql.append(" and  allianceName in ('谷歌ADX广告') ");
		sql.append(" and source = '58' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols11 = new ArrayList<String>();
		cols11.add("statDate");
		cols11.add("googleADXIncome");
		sqlMap.put(sql.toString(),cols11 );
		
		// 冉十的收入总额
		sql.delete(0, sql.length()+1);
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"' " );
		sql.append(" and  allianceName = '冉十科技' ");
		sql.append(" and source = '58' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols12 = new ArrayList<String>();
		cols12.add("statDate");
		cols12.add("rsIncome");
		sqlMap.put(sql.toString(),cols12 );
		
		// 小猪的收入总额
		sql.delete(0, sql.length()+1);
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"' " );
		sql.append(" and  allianceName = '小猪' ");
		sql.append(" and source = '58' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols13 = new ArrayList<String>();
		cols13.add("statDate");
		cols13.add("xiaozhuIncome");
		sqlMap.put(sql.toString(),cols13 );
		
		
		//360联盟的收入总额
		sql.delete(0, sql.length()+1);
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"' " );
		sql.append(" and  allianceName = '360联盟' ");
		sql.append(" and source = '58' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols14 = new ArrayList<String>();
		cols14.add("statDate");
		cols14.add("qihuIncome");
		sqlMap.put(sql.toString(),cols14 );
		
		
		//直客广告的收入总额
		sql.delete(0, sql.length()+1);
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"' " );
		sql.append(" and  allianceName = '直客广告' ");
		sql.append(" and source = '58' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols15 = new ArrayList<String>();
		cols15.add("statDate");
		cols15.add("zkIncome");
		sqlMap.put(sql.toString(),cols15 );
		
		
		//搜搜的收入总额
		sql.delete(0, sql.length()+1);
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"' " );
		sql.append(" and  allianceName = '搜搜' ");
		sql.append(" and source = '58' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols16 = new ArrayList<String>();
		cols16.add("statDate");
		cols16.add("sousouIncome");
		sqlMap.put(sql.toString(),cols16 );
		
		
		//品牌广告的收入总额
		sql.delete(0, sql.length()+1);
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"' " );
		sql.append(" and  allianceName = '品牌' ");
		sql.append(" and source = '58' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols17 = new ArrayList<String>();
		cols17.add("statDate");
		cols17.add("pinpaiIncome");
		sqlMap.put(sql.toString(),cols17 );
		
		
		return sqlMap;
	}
	
	
	//58摊销数据
	private Map<String,List<String>>  amortizeDataSqlFor58(String date){
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		//余额摊销总收入合计、余额消耗、余额现金消耗
		sql.append(" SELECT vDate,sum(dayAmortize),sum(payCashCost)+sum(payGiftCost),sum(payCashCost)   ");
		sql.append(" FROM `amortize_metrics` " );
		sql.append(" where  memberId in("+memberIdAll58+")  ");
		sql.append(" and vDate = '"+date+"' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols1 = new ArrayList<String>();
		cols1.add("statDate");
		cols1.add("balanceAmortize58");
		cols1.add("balanceCost");
		cols1.add("balanceCashCost");
		sqlMap.put(sql.toString(),cols1);
		
		
		//置顶系统摊销总收入合计、余额消耗、余额现金消耗
		sql.delete(0, sql.length()+1);
		sql.append(" SELECT vDate,sum(dayAmortize),sum(payCashCost)+sum(payGiftCost),sum(payCashCost)   ");
		sql.append(" FROM `amortize_metrics` " );
		sql.append(" where  memberId ='1022' ");
		sql.append(" and vDate = '"+date+"' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols2 = new ArrayList<String>();
		cols2.add("statDate");
		cols2.add("topAmortize");
		cols2.add("topCost");
		cols2.add("topCashCost");
		sqlMap.put(sql.toString(),cols2 );
		
		//精准推广摊销总收入合计、余额消耗、余额现金消耗
		sql.delete(0, sql.length()+1);
		sql.append(" SELECT vDate,sum(dayAmortize),sum(payCashCost)+sum(payGiftCost),sum(payCashCost)   ");
		sql.append(" FROM `amortize_metrics` " );
		sql.append(" where  memberId ='1126' ");
		sql.append(" and vDate = '"+date+"' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols3 = new ArrayList<String>();
		cols3.add("statDate");
		cols3.add("preAmortize");
		cols3.add("preCost");
		cols3.add("preCashCost");
		sqlMap.put(sql.toString(),cols3 );
		
		
		//智能推广摊销总收入合计、余额消耗、余额现金消耗
		sql.delete(0, sql.length()+1);
		sql.append(" SELECT vDate,sum(dayAmortize),sum(payCashCost)+sum(payGiftCost),sum(payCashCost)   ");
		sql.append(" FROM `amortize_metrics` " );
		sql.append(" where  memberId ='7202' ");
		sql.append(" and vDate = '"+date+"' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols4 = new ArrayList<String>();
		cols4.add("statDate");
		cols4.add("intAmortize");
		cols4.add("intCost");
		cols4.add("intCashCost");
		sqlMap.put(sql.toString(),cols4 );
		
		
		//精准点击付费摊销收入合计
		sql.delete(0, sql.length()+1);
		sql.append(" SELECT vDate,sum(dayAmortize),sum(payCashCost)+sum(payGiftCost),sum(payCashCost)   ");
		sql.append(" FROM `amortize_metrics` " );
		sql.append(" where  memberId in ('7023','1092') ");
		sql.append(" and vDate = '"+date+"' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols5 = new ArrayList<String>();
		cols5.add("statDate");
		cols5.add("preClickAmortize");
		cols5.add("preClickCost");
		cols5.add("preClickCash");
		
		sqlMap.put(sql.toString(),cols5 );
		
		//招聘摊销收入合计 、余额消耗、余额现金消耗
		sql.delete(0, sql.length()+1);
		sql.append(" SELECT vDate,sum(dayAmortize),sum(payCashCost)+sum(payGiftCost),sum(payCashCost)  ");
		sql.append(" FROM `amortize_metrics` " );
		sql.append(" where  memberId in(1032,1037,1038,1077,1096,1097,1104,1105) "); 
		sql.append(" and vDate = '"+date+"' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols6 = new ArrayList<String>();
		cols6.add("statDate");
		cols6.add("jobAmortize");
		cols6.add("jobCost");
		cols6.add("jobCashCost");
		
		sqlMap.put(sql.toString(),cols6 );
		
		//刷新摊销收入合计
		sql.delete(0, sql.length()+1);
		sql.append(" SELECT vDate,sum(dayAmortize)  ");
		sql.append(" FROM `amortize_metrics` " );
		sql.append(" where  memberId in( '1028','1081','1127') ");
		sql.append(" and vDate = '"+date+"' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols7 = new ArrayList<String>();
		cols7.add("statDate");
		cols7.add("refreshAmortize");
		sqlMap.put(sql.toString(),cols7 );
		
		
		//品牌推广-直销
		sql.delete(0, sql.length()+1);
		sql.append(" SELECT vDate,sum(dayAmortize)  ");
		sql.append(" FROM `amortize_metrics` " );
		sql.append(" where  memberId in(1114,1115,1116,1117,1118,1119,1146,1152,1164,1165)  ");
		sql.append(" and vDate = '"+date+"' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols8 = new ArrayList<String>();
		cols8.add("statDate");
		cols8.add("mpBrandProAmortize");
		sqlMap.put(sql.toString(),cols8 );
		
		//广告大客户品牌推广
		sql.delete(0, sql.length()+1);
		sql.append(" SELECT vDate,sum(dayAmortize)  ");
		sql.append(" FROM `amortize_metrics` " );
		sql.append(" where  memberId in(1147)  ");
		sql.append(" and vDate = '"+date+"' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols13 = new ArrayList<String>();
		cols13.add("statDate");
		cols13.add("ggdkhAmortize");
		sqlMap.put(sql.toString(),cols13 );
		
		
		//优先类别摊销
		sql.delete(0, sql.length()+1);
		sql.append(" SELECT vDate,sum(dayAmortize)  ");
		sql.append(" FROM `amortize_metrics` " );
		sql.append(" where  memberId in(1108,1082,1120,1094,1106,1121)  ");
		sql.append(" and vDate = '"+date+"' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols9 = new ArrayList<String>();
		cols9.add("statDate");
		cols9.add("priorityAmortize");
		sqlMap.put(sql.toString(),cols9 );
		
		
		//其他产品摊销合计
		sql.delete(0, sql.length()+1);
		sql.append(" SELECT vDate,sum(dayAmortize)  ");
		sql.append(" FROM `amortize_metrics` " );
		sql.append(" where  memberId in(1019,1034,1039,1071,1088,1113,1128,1155,1156,1149) "); 
		sql.append(" and vDate = '"+date+"' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols10 = new ArrayList<String>();
		cols10.add("statDate");
		cols10.add("otherAmortize");
		sqlMap.put(sql.toString(),cols10 );
		
		
		
//		/**拆分品牌推广摊销  给分析那边在BI用 */
//		//品牌直销
//		sql.delete(0, sql.length()+1);
//		sql.append(" SELECT vDate,sum(dayAmortize)  ");
//		sql.append(" FROM `amortize_metrics` " );
//		sql.append(" where  memberId in(1114,1115,1116,1117,1118,1119,1164,1165)  ");
//		sql.append(" and vDate = '"+date+"' ");
//		sql.append(" :"+DBConfig.db_68_ors.getIntType());
//		
//		List<String> cols11 = new ArrayList<String>();
//		cols11.add("statDate");
//		cols11.add("brandZX");
//		sqlMap.put(sql.toString(),cols11 );
//		
//		//品牌KA摊销
//		sql.delete(0, sql.length()+1);
//		sql.append(" SELECT vDate,sum(dayAmortize)  ");
//		sql.append(" FROM `amortize_metrics` " );
//		sql.append(" where  memberId in(1146,1147)  ");
//		sql.append(" and vDate = '"+date+"' ");
//		sql.append(" :"+DBConfig.db_68_ors.getIntType());
//		
//		List<String> cols12 = new ArrayList<String>();
//		cols12.add("statDate");
//		cols12.add("brandKA");
//		sqlMap.put(sql.toString(),cols12 );
		
		return sqlMap;
	}
	
	
	/**驾校一点通数据
	 * @param date
	 * @return
	 */
	private Map<String,List<String>> sqlForJX(String date)throws Exception{
		
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		//驾校一点通摊销收入
		sql.append(" SELECT vDate,sum(dayAmortize)  ");
		sql.append(" FROM amortize_metrics " );
		sql.append(" where  memberId in('1134','1153')  ");
		sql.append(" and vDate = '"+date+"' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols1 = new ArrayList<String>();
		cols1.add("statDate");
		cols1.add("amortizeIncomeJX");
		sqlMap.put(sql.toString(),cols1);
		
		
		//百度的收入总额
		sql.delete(0, sql.length()+1);
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"' " );
		sql.append(" and  allianceName = '百度' ");
		sql.append(" and channel ='总计' ");
		//驾校一点通 百度 汇总计算 处理  ： 只计算wm_all的  从2015.11.1开始    -- by HL
		if(DateUtil.strToDate(date, "yyyy-MM-dd").after(DateUtil.strToDate("2015-10-31", "yyyy-MM-dd"))){
			sql.append(" and billingName = 'wm_all' ");
		}
		sql.append(" and source = 'jxydt' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols2 = new ArrayList<String>();
		cols2.add("statDate");
		cols2.add("baiduJX");
		sqlMap.put(sql.toString(),cols2 );
		
		
		//淘宝的收入总额
		sql.delete(0, sql.length()+1);
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"' " );
		sql.append(" and  allianceName = '淘宝' ");
		sql.append(" and source = 'jxydt' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols3 = new ArrayList<String>();
		cols3.add("statDate");
		cols3.add("taobaoJX");
		sqlMap.put(sql.toString(),cols3 );
		
		//易特的收入总额
		sql.delete(0, sql.length()+1);
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"' " );
		sql.append(" and  allianceName = '易特' ");
		sql.append(" and source = 'jxydt' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols4 = new ArrayList<String>();
		cols4.add("statDate");
		cols4.add("yiteJX");
		sqlMap.put(sql.toString(),cols4 );
		
		
		//联告的收入总额
		sql.delete(0, sql.length()+1);
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"' " );
		sql.append(" and  allianceName = '联告' ");
		sql.append(" and source = 'jxydt' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols5 = new ArrayList<String>();
		cols5.add("statDate");
		cols5.add("liangaoJX");
		sqlMap.put(sql.toString(),cols5 );
		
		//一起赢的收入总额
		sql.delete(0, sql.length()+1);
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"' " );
		sql.append(" and  allianceName = '一起赢' ");
		sql.append(" and source = 'jxydt' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols6 = new ArrayList<String>();
		cols6.add("statDate");
		cols6.add("yiqiyingJX");
		sqlMap.put(sql.toString(),cols6 );
		
		
		//谷歌ADX广告的收入总额
		sql.delete(0, sql.length()+1);
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"' " );
		sql.append(" and  allianceName = '谷歌ADX广告' ");
		sql.append(" and source = 'jxydt' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols7 = new ArrayList<String>();
		cols7.add("statDate");
		cols7.add("googleadxJX");
		sqlMap.put(sql.toString(),cols7 );
		
		
		//谷歌ADX广告的收入总额
		sql.delete(0, sql.length()+1);
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"' " );
		sql.append(" and  allianceName = '直客广告' ");
		sql.append(" and source = 'jxydt' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols8 = new ArrayList<String>();
		cols8.add("statDate");
		cols8.add("zkJX");
		sqlMap.put(sql.toString(),cols8 );
		
		return sqlMap;
	}
	
	
	/**计算驾校一点 联盟总收入
	 * 
	 * @param date
	 * @throws Exception
	 */
	private void countAllIncomeForJX(String date)throws Exception{
		StringBuffer sql = new StringBuffer();
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"'  ");
		sql.append(" and  allianceName  not in ('谷歌','百度')  " );
		sql.append(" and source = 'jxydt'");
		
		Map<String,String>  rsMap = comDao.selectValueAsMap(sql.toString(), DBConfig.db_68_ors.getIntType());
		if(rsMap.containsKey(date)){
			allIncomeJxydt = Double.parseDouble ( rsMap.get(date) ) + googleAFSJxydt + googleAFCJxydt ;
		}else{
			allIncomeJxydt = googleAFSJxydt + googleAFCJxydt ;
		}
		
		//百度的
		sql.delete(0, sql.length()+1);
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"' " );
		sql.append(" and  allianceName = '百度' ");
		sql.append(" and channel ='总计' ");
		
		//驾校一点通 百度 汇总计算 处理  ： 只计算wm_all的  从2015.11.1开始    -- by HL
		if(DateUtil.strToDate(date, "yyyy-MM-dd").after(DateUtil.strToDate("2015-10-31", "yyyy-MM-dd"))){
			sql.append(" and billingName = 'wm_all' ");
		}
		
		sql.append(" and source = 'jxydt' ");
		Map<String,String>  rsMapBaidu = comDao.selectValueAsMap(sql.toString(), DBConfig.db_68_ors.getIntType());
		
		if(rsMapBaidu.containsKey(date)){
			allIncomeJxydt = Double.parseDouble ( rsMapBaidu.get(date) ) + allIncomeJxydt;
		}
	}
	
	
	
	private double baiduAFCGJ = 0;//赶集百度AFC总收入
	private double baiduAFSGJ = 0;//赶集百度AFS总收入
	private double allianceIncomeGJ = 0;//赶集联盟总收入
	
	/**赶集联盟总收入  &  百度收入计算
	 */
	private void countAllGJIncome(String date)throws Exception{
		
		//正常收入汇总
		StringBuffer sql = new StringBuffer();
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"'  ");
		sql.append(" and  allianceName  not in ('谷歌','百度')  " );
		sql.append(" and source = 'gj'");
		
		Map<String,String>  rsMap = comDao.selectValueAsMap(sql.toString(), DBConfig.db_68_ors.getIntType());
		if(rsMap.containsKey(date)){
			allianceIncomeGJ = Double.parseDouble ( rsMap.get(date) ) ;
		}
		
		//赶集百度收入  
		//AFC = 计费名渠道为总计&广告类型为AFC的总计 - wm_all
		sql.delete(0, sql.length()+1);
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"' " );
		sql.append(" and  allianceName = '百度' ");
		sql.append(" and channel ='总计' ");
		sql.append(" and advertType = 'AFC'");
		sql.append(" and  billingName not in ('wm_all') ");
		sql.append(" and source = 'gj' ");
		Map<String,String>  rsMapBaiduAFC = comDao.selectValueAsMap(sql.toString(), DBConfig.db_68_ors.getIntType());
		
		if(rsMapBaiduAFC.containsKey(date)){
			baiduAFCGJ = Double.parseDouble ( rsMapBaiduAFC.get(date) );
		}
		
		//AFS = 计费名渠道为总计&广告类型为AFS的总计 - ss_all
		sql.delete(0, sql.length()+1);
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"' " );
		sql.append(" and  allianceName = '百度' ");
		sql.append(" and channel ='总计' ");
		sql.append(" and advertType = 'AFS'");
		sql.append(" and  billingName not in ('ss_all') ");
		sql.append(" and source = 'gj' ");
		Map<String,String>  rsMapBaiduAFS = comDao.selectValueAsMap(sql.toString(), DBConfig.db_68_ors.getIntType());
		
		if(rsMapBaiduAFS.containsKey(date)){
			baiduAFSGJ = Double.parseDouble ( rsMapBaiduAFS.get(date) );
		}
		
		
		allianceIncomeGJ = allianceIncomeGJ + baiduAFCGJ + baiduAFSGJ;
	}
	
	
	/**赶集正常联盟收入明细计算
	 */
	private Map<String,List<String>> countGJIncome(String date)throws Exception{
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		//赶集京东收入
		sql.delete(0, sql.length()+1);
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"' " );
		sql.append(" and  allianceName = '京东' ");
		sql.append(" and source = 'gj' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols1 = new ArrayList<String>();
		cols1.add("statDate");
		cols1.add("jdGJ");
		sqlMap.put(sql.toString(),cols1 );
		
		//赶集搜狗收入
		sql.delete(0, sql.length()+1);
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"' " );
		sql.append(" and  allianceName = '搜狗' ");
		sql.append(" and source = 'gj' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols2 = new ArrayList<String>();
		cols2.add("statDate");
		cols2.add("sougouGJ");
		sqlMap.put(sql.toString(),cols2 );
		
		//赶集三端总PV
		sql.delete(0, sql.length()+1);
		sql.append(" select stat_date,sum(case when page_type='-' then pv else 0  end) as 'pv' ");
		sql.append(" from data_tmp.t_netflow_ganji_" +  date.substring(0,date.indexOf("-")));
		sql.append(" where business_type='-' and platform IN ('app','wap','web') ");
		sql.append(" and cate1='-' and source1='-' ");
		sql.append(" and stat_date = '"+date+"' " );
		
		sql.append(" :"+DBConfig.db_94_data_app.getIntType());
		
		List<String> cols3 = new ArrayList<String>();
		cols3.add("statDate");
		cols3.add("allPvGJ");
		sqlMap.put(sql.toString(),cols3 );
		
		
		//赶集谷歌ADX收入
		sql.delete(0, sql.length()+1);
		sql.append(" select statDate,sum(amount) from alliance_billing_metrics ");
		sql.append(" where statDate = '"+date+"' " );
		sql.append(" and  allianceName = '谷歌ADX广告' ");
		sql.append(" and source = 'gj' ");
		sql.append(" :"+DBConfig.db_68_ors.getIntType());
		
		List<String> cols4 = new ArrayList<String>();
		cols4.add("statDate");
		cols4.add("googleADXGJ");
		sqlMap.put(sql.toString(),cols4 );
		
		return sqlMap;
	}
	
	
	/**根据起始日期 计算每日的在线收入汇总数据
	 * @param beginDate 
	 * @param endDate
	 * @param type   月收入总和的计算方式  0：预估  1：加和已有全月收入数据
	 * @return
	 */
	public boolean  dealData(String beginDate , String endDate , int type){
		try{
			List<String>  dateList = new ArrayList<String>();
			while(true){
				dateList.add(beginDate);
				if(beginDate.equals(endDate)){
					break;
				}
				beginDate = DateUtil.getStringDate(DateUtil.getDateAfter(DateUtil.strToDate(beginDate, "yyyy-MM-dd"), 1), "yyyy-MM-dd");
			}
			
			List<String> gbList = new ArrayList<String>();
			gbList.add("statDate");
			
			
			for(String date : dateList){
				List<AllianceDataSummaryVo> finalList  = new ArrayList<AllianceDataSummaryVo>();
				System.out.println("begin deal : "+date);
				
				//58联盟收入SQL
				Map<String,List<String>> sqlMap1 = allianceDataSqlFor58(date,type);
				
				//58余额摊销收入SQL
				Map<String,List<String>> sqlMap2 = amortizeDataSqlFor58(date);
				sqlMap1.putAll(sqlMap2);
				
				//驾校一点通SQL
				Map<String,List<String>> sqlMap3 = sqlForJX(date);
				sqlMap1.putAll(sqlMap3);
				
				//赶集明细数据SQL
				Map<String,List<String>> sqlMap4 = countGJIncome(date);
				sqlMap1.putAll(sqlMap4);
				
				//SQL计算
				List<AllianceDataSummaryVo> rsList = AssembledDataBySqlMap(sqlMap1, AllianceDataSummaryVo.class, gbList);
				//去除日期空数据
				if(rsList.size() > 1){
					for(AllianceDataSummaryVo vo : rsList){
						String statDate = vo.getStatDate();
						if(null == statDate || "".equals(statDate)){
							continue;
						}else{
							finalList.add(vo);
						}
					}
				}else{
					finalList = rsList;
				}
				
				AllianceDataSummaryVo vo = finalList.get(0);
				
				//计算未靠SQL查询的值
				dealSpecFor58(date,type); //计算58特殊计费名分成收入
				
				countBaiduFor58(date); //计算58百度总收入
				vo.setBaiduAFCIncome(baiduAFC58);
				vo.setBaiduAFSIncome(baiduAFS58);
				vo.setBaiduAPPIncome(baiduAPP58);
				
				countGoogle(date,"58"); // 计算58谷歌收入
				vo.setGoogleAFCIncome(googleAFC58);
				vo.setGoogleAFSIncome(googleAFS58);
				
				countSougouFor58(date);// 计算58搜狗收入
				vo.setSougouIncome(sougouIncome);
				
				countAllIncomeFor58(date); //计算58联盟总收入
				vo.setAllianIncome58(allIncome58);
				
				//计算驾校一点通谷歌收入数据
				countGoogle(date, "jxydt"); 
				vo.setGoogleJX(googleAFCJxydt + googleAFSJxydt);
				//驾校一点通联盟总收入
				countAllIncomeForJX(date);
				vo.setAllianceIncomeJX(allIncomeJxydt);
				
				//驾校一点通 总收入 = 驾校一点通联盟　+　驾校一点通摊销
				vo.setAllIncomeJX(vo.getAllianceIncomeJX()  + vo.getAmortizeIncomeJX());
				
				//其他
				vo.setAdverPvRate((double) vo.getAdverPv() / vo.getAllWebPv());
				vo.setCPC((double) vo.getAllianIncome58() / vo.getClicksAll());
				vo.setECPM((double) (vo.getAllianIncome58()*1000) / vo.getShowPvAll() );
				vo.setCTR((double) vo.getClicksAll() / vo.getShowPvAll());
				
				
				//58在线收入总收入 = 58联盟 + 58摊销 
				vo.setOnlineRevenueAll(vo.getAllianIncome58() + vo.getBalanceAmortize58());
				
				
				//赶集汇总数据&百度收入
				countAllGJIncome(date);
				vo.setAllianceIncomeGJ(allianceIncomeGJ);//赶集联盟总收入
				vo.setBaiduAFCGJ(baiduAFCGJ);//赶集百度AFC收入
				vo.setBaiduAFSGJ(baiduAFSGJ);//赶集百度AFS收入
				
				if(!finalList.isEmpty() ){
					String filePath = PathUtil.getWFNamespaceFolder()+"/dbrecord/AllianceDataSummary"+beginDate+"-"+endDate+".txt";
					String reg = "^&";
					filePath = fileUtil.writeFileByVo(filePath, finalList, AllianceDataSummaryVo.class, reg,true);
					comDao.loadData(filePath, "online_revenue_day", reg, DBConfig.db_68_ors.getIntType());
					System.out.println("has loaded in "+finalList.size());
				}
			}
			System.out.println("done~");
		}catch (Exception e) {
			e.printStackTrace();
			SomePublicUtil.emailWarn("在线收入每日数据汇总更新异常！");
			return false;
		}
		return true;
	}
	
	
	public static void main(String[] args) {
		AllianceDataSummary  deal = new AllianceDataSummary();
//		String beginDate = "2015-11-01";
//		String endDate = "2015-11-17";
		String beginDate = DateUtil.getStringDate(DateUtil.getDateBefore(new Date(), 1), "yyyy-MM-dd");
		String endDate =  DateUtil.getStringDate(DateUtil.getDateBefore(new Date(), 1), "yyyy-MM-dd");
		try{
			CommonDao comDao = new CommonDao();
			String deleteCon = " and statDate >= '"+beginDate+"' and statDate <='"+endDate+"' ";
			comDao.deleteData("online_revenue_day", deleteCon, DBConfig.db_68_ors.getIntType());
			
			deal.dealData(beginDate, endDate,0);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			System.exit(0);
		}
	
		
	}
	
}
